package snapex.core.mapper.ext;

import org.apache.ibatis.builder.annotation.ProviderMethodResolver;

public class ExpenseSqlProvider implements ProviderMethodResolver{

	public static String updateStaffId() {		
		return "update expense set staff_id = (select staff_id from employee_info where hash_key=username) where staff_id is null";		
	}
	
	public static String updateExtensionNumber() {		
		return "update expense set extension_no = (select extension_no from employee_info where hash_key=username) where extension_no is null";		
	}
	
	public static String updateNextApprover() {		
		return "update expense set next_approver = (select manager_id from employee_info where hash_key=username) where next_approver is null";		
	}
	
	public static String updateSyncTimetamp() {		
		return "update expense set sync_timestamp = current_timestamp where year(sync_timestamp) > year(current_timestamp)";		
	}
	
	public static String markSyncTimetamp() {		
		return "update expense set sync_timestamp = date_add(current_timestamp,interval 1 year) where sync_timestamp is null";		
	}
	
	public static String rollbackSyncTimetamp() {		
		return "update expense set sync_timestamp = null where year(sync_timestamp) > year(current_timestamp)";	
	}
	
	public static String updateCostCenterMandatory() {		
		return "update expense e set e.customer_cost_centre_mandatory = (select pc_cost_center_mandatory from t_project_customer_info p where e.clarity_project_id=p.pc_gmis_project_id) where e.sync_timestamp is null";		
	}
	
	public static String updateCostCenterName() {		
		return "update expense e set e.customer_cost_centre_name = (select pc_customer_name from t_project_customer_info p where e.clarity_project_id=p.pc_gmis_project_id) where e.sync_timestamp is null";		
	}	
			
	public static String updateCostCenter() {		
		return "update expense e set e.customer_cost_centre_no=(select g.ugp_cost_centre from t_user_groups g, t_staff_info s where g.ugp_name = s.stf_teamid and s.stf_no=e.staff_id) where e.sync_timestamp is null";		
	}
	
	public static String insertExpense() {
		return "insert into expense (expense_id, status, next_approver, last_approver, staff_id, extension_no, site_id, total_amount, expense_cash_advance_amount, submitted_date, confirmed_date, clarity_project_id, clarity_project_name, customer_cost_centre_name, customer_cost_centre_no, billable, customer_cost_centre_mandatory, username, description, create_timestamp, sync_timestamp) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	}
	
	public static String insertExpenseDetail() {
		return "insert into expense_detail (expense_detail_id, expense_id, occur_date, description, currency, amount, purpose_id, id, list_index, invoice_id, status, create_timestamp, sync_timestamp) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	}	
}
